{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "wl = openpyxl.load_workbook(r'C:\\Users\\fei\\code\\emms\\downloads\\开户\\2022-3-7开户汇总 (2).xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws = wl['黄冈中星0307AB']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(ws['K267'].value) == str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UserWarning: Workbook contains no default style, apply openpyxl's default\n"
     ]
    }
   ],
   "source": [
    "fs2 = pandas.read_excel(r'C:\\Users\\fei\\code\\emms\\downloads\\外呼\\2022-02-28+xy+襄阳市.xlsx', index_col=None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_pyecharts_user(x,data):#\n",
    "    df_user_count = data.groupby(['时段','地市'])['处理人工号'].nunique()\n",
    "    df_user_count_1 = data.groupby(['时段','部门'])['处理人工号'].nunique()\n",
    "    city_list = data['地市'].unique().tolist()\n",
    "    team_list = data['部门'].unique().tolist()\n",
    "    bar = (\n",
    "        Bar()\n",
    "        .add_xaxis(x)\n",
    "        .set_global_opts(\n",
    "            title_opts=opts.TitleOpts(title='人力分布-团队/地市'),\n",
    "        )\n",
    "    )\n",
    "    for team_name in team_list:\n",
    "        y_data = []\n",
    "        for time_1 in time_list:\n",
    "            try:\n",
    "                y_data.append(int(df_user_count_1.loc[(time_1,team_name)]))\n",
    "            except:\n",
    "                y_data.append(0)\n",
    "        bar.add_yaxis(team_name, y_data, stack=team_name,label_opts=opts.LabelOpts(is_show=0))#label_opts=opts.LabelOpts(is_show=0)\n",
    "    line = Line()\n",
    "    line .add_xaxis(x)\n",
    "    for city_name in city_list:\n",
    "        y_data = []\n",
    "        for time_1 in time_list:\n",
    "            try:\n",
    "                y_data.append(int(df_user_count.loc[(time_1,city_name)]))\n",
    "            except:\n",
    "                y_data.append(0)\n",
    "        line .add_yaxis(city_name, y_data, \n",
    "            linestyle_opts=opts.LineStyleOpts(width=2), \n",
    "            is_smooth=True)\n",
    "    bar.overlap(line)\n",
    "    return bar\n",
    "\n",
    "def create_pyecharts_success(x,data):\n",
    "    grid = Grid()\n",
    "    df_user_count = data.groupby(['时段','任务'])['处理人工号'].nunique()\n",
    "    task_list = data['任务'].unique().tolist()\n",
    "    c_count1 = data[data[\"通话时长(秒)\"]>0].groupby(['时段','任务']).count()[\"被叫\"] # 大于0秒的数量(分项目)\n",
    "    s_count1 = data[data[\"用户意向\"]==\"成功\"].groupby(['时段','任务']).count()[\"被叫\"] # 用户意向成功的数量(分项目)\n",
    "    df = pd.concat([c_count1,s_count1], axis=1)  # 按列索引合并\n",
    "    # df.reset_index(inplace=True)\n",
    "    df.columns=['接通','成功']\n",
    "    df = df[df[\"成功\"].notnull()]\n",
    "    df['成功率'] = df['成功'] / df['接通'] * 100\n",
    "\n",
    "    bar = Bar()#成功量\n",
    "    bar.add_xaxis(x).set_global_opts(title_opts=opts.TitleOpts(title='成功量分布-任务'),\n",
    "    legend_opts=opts.LegendOpts(type_=\"scroll\", pos_left=\"right\", orient=\"vertical\"),)\n",
    "    bar.extend_axis(yaxis=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter=\"{value} %\"),interval=5))\n",
    "    line = Line()#成功率\n",
    "    line.add_xaxis(x)\n",
    "    scatter = Scatter()#人数\n",
    "    scatter.add_xaxis(x)\n",
    "    scatter.set_global_opts(visualmap_opts=opts.VisualMapOpts(type_=\"size\", max_=150, min_=20),)\n",
    "\n",
    "    for task_name in task_list:#成功量y成功率y\n",
    "        suc_data = []\n",
    "        suc1_data = []\n",
    "        for time_1 in time_list:\n",
    "            try:\n",
    "                suc_data.append(int(df.loc[(time_1,task_name)]['成功']))\n",
    "            except:\n",
    "                suc_data.append(0)\n",
    "            try:\n",
    "                suc1_data.append(int(df.loc[(time_1,task_name)]['成功率']))\n",
    "            except:\n",
    "                suc1_data.append(0)\n",
    "        scatter.add_yaxis(task_name, suc_data,is_selected=0,color=\"green\")\n",
    "        line.add_yaxis(task_name, suc1_data, \n",
    "            linestyle_opts=opts.LineStyleOpts(width=2), \n",
    "            is_smooth=True, is_selected=0,yaxis_index=1)\n",
    "\n",
    "    for task_name in task_list:\n",
    "        y_data = []\n",
    "        for time_1 in time_list:\n",
    "            try:\n",
    "                y_data.append(int(df_user_count.loc[(time_1,task_name)]))\n",
    "            except:\n",
    "                y_data.append(0)\n",
    "        bar.add_yaxis(task_name, y_data, is_selected=0, bar_width=\"5px\")\n",
    "\n",
    "    bar.overlap(line)\n",
    "    bar.overlap(scatter)\n",
    "    grid.add(bar,grid_opts=opts.GridOpts(pos_top=\"50%\"),is_control_axis_index=True)\n",
    "    return grid\n",
    "\n",
    "def create_pyecharts_table(fs):\n",
    "    groupby_v = [\"日期\",\"部门\",\"任务\"]\n",
    "    cc_count1 = fs.groupby(groupby_v).count()[\"被叫\"] # 拨打量(分项目)\n",
    "    c_count1 = fs[fs[\"通话时长(秒)\"]>0].groupby(groupby_v).count()[\"被叫\"] # 大于0秒的数量(分项目)\n",
    "    s_count1 = fs[fs[\"用户意向\"]==\"成功\"].groupby(groupby_v).count()[\"被叫\"] # 用户意向成功的数量(分项目)\n",
    "    users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)\n",
    "    df1 = pd.concat([cc_count1,c_count1,s_count1,users_count], axis=1)  # 按列索引合并\n",
    "    # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')\n",
    "    # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行\n",
    "    df1.reset_index(inplace=True)\n",
    "    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数']\n",
    "    df1['接通率'] = df1['接通'] / df1['拨打量']\n",
    "    df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))\n",
    "    df1['成功率'] = df1['成功'] / df1['接通']\n",
    "    df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))       \n",
    "    df1 = df1[df1[\"成功\"].notnull()]\n",
    "    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']\n",
    "    res = []\n",
    "    for team1_name in team1_dic:\n",
    "        df2 = df1[df1['部门'].isin(team1_dic[team1_name])]\n",
    "        if df2.empty is not True:\n",
    "            df2.loc['合计'] = ['合计','','',df1['拨打量'].sum(),df1['接通'].sum(),df1['成功'].sum(),'','','']\n",
    "            table = Table()\n",
    "            headers = df2.columns.to_list()\n",
    "            rows = df2.values.tolist()\n",
    "            table.add(headers, rows)\n",
    "            table.set_global_opts({\"title\":\"效能-\"+team1_name, \n",
    "                            \"subtitle\":str(datetime.datetime.today())[:19],\n",
    "                            \"title_style\":\"style='color:red'\",\n",
    "                            \"subtitle_style\":\"style='color:green'\"\n",
    "                            })\n",
    "            res.append(table)\n",
    "    return res\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "theme = ThemeType.DARK\n",
    "page = Page(layout=Page.DraggablePageLayout)\n",
    "for i in create_pyecharts_table(fs):\n",
    "    page.add(i)\n",
    "page.add(\n",
    "    create_pyecharts_user(time_list,fs),\n",
    "    create_pyecharts_success(time_list,fs)\n",
    "    )\n",
    "page.render('./templates/render.html')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('py39')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "131197ad4fc75bdeabf186986f47f6803bcba6b55398f408712b233a75927610"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
